package ShenBlueInc.Sql;

import java.sql.ResultSet;

import ShenBlueTool.isString;

public class Card_inc {
	SqlConnect conn=new SqlConnect();
	
	/**
	 * 根据用户添加信用卡信息
	 * @param id
	 * @param card_id
	 * @param time
	 * @param Bank_id
	 * @return
	 */
	public int Add_Card(String id,String card_id,String time,String Bank_id) {
		String sql="insert CreditCard(User_id,CreditCard_number,CreditCard_date,Bank_id) values(?,?,?,?)";
		return conn.DelUpInsert(sql, id,card_id,time,Bank_id);
	}
	/**
	 * 查询指定用户下是否有该信用卡卡号
	 * @param id
	 * @param card_id
	 * @return
	 */
	public int SelectCardID(String id,String card_id) {
		String sql="select count(CreditCard_id) from CreditCard where User_id=? and CreditCard_number=?";
		return Integer.parseInt(conn.OneItem(sql, id,card_id).toString());
	}
	/**
	 * 查询前五天后五天内需要还款的卡
	 * @param user_id
	 * @param page
	 * @param limit
	 * @return
	 */
	public ResultSet TipsCrad(String user_id,String page,String limit) {
		String sql="SELECT\r\n" + 
				"	cdc.CreditCard_number,\r\n" + 
				"	DATE_FORMAT( cdc.CreditCard_date, '%Y-%m-%d' ),\r\n" + 
				"IF\r\n" + 
				"	( cdc.CreditCard_date = rp.Repayment_date, '以还款', '未还款' ) \r\n" + 
				"FROM\r\n" + 
				"	CreditCard AS cdc\r\n" + 
				"	LEFT JOIN Repayment AS rp ON cdc.CreditCard_id = rp.CreditCard_id \r\n" + 
				"WHERE\r\n" + 
				"	cdc.User_id = ? \r\n" + 
				"	AND ( DATE_SUB(cdc.CreditCard_date , INTERVAL 5 DAY )<= now() \r\n" + 
				"	AND DATE_ADD(cdc.CreditCard_date , INTERVAL 5 DAY )>=now()  ) \r\n" + 
				"	AND ( SELECT count(*) FROM `Repayment` WHERE Repayment_date=cdc.CreditCard_date and CreditCard_id=cdc.CreditCard_id AND User_id = ? ) = 0" + 
				" GROUP BY " + 
				"	cdc.CreditCard_id \r\n" + 
				" ORDER BY\r\n" + 
				"	cdc.CreditCard_date "+ 
				"LIMIT "+page+","+limit;
		ResultSet rs=conn.Items(sql, user_id,user_id);
		return rs;
	}
	/**
	 * 返回查询的总数
	 * @param user_id
	 * @return
	 */
	public int TipsCradCount(String user_id) {
		String sql="SELECT\r\n" + 
				"	COUNT(*)\r\n" + 
				"FROM\r\n" + 
				"	CreditCard AS cdc\r\n" + 
				"	 \r\n" + 
				"WHERE\r\n" + 
				"	cdc.User_id = ? \r\n" + 
				"	AND ( DATE_SUB(cdc.CreditCard_date , INTERVAL 5 DAY )<= now() \r\n" + 
				"	AND DATE_ADD(cdc.CreditCard_date , INTERVAL 5 DAY )>=now()  ) \r\n" + 
				"	AND ( SELECT count(*) FROM `Repayment` WHERE Repayment_date=cdc.CreditCard_date and CreditCard_id=cdc.CreditCard_id AND User_id = ? ) =0";
		return Integer.parseInt(conn.OneItem(sql, user_id,user_id).toString());
	}
	/**
	 * 标记还款
	 * @param user_id
	 * @param card_id
	 * @return
	 */
	public int TipsCardRepay(String user_id,String card_id) {
		String sql="insert into Repayment(CreditCard_id,Repayment_date,User_id) values((select CreditCard_id from CreditCard where CreditCard_number=? and User_id=?),(select CreditCard_date from CreditCard where CreditCard_number=? and User_id=?),?)";
		return conn.DelUpInsert(sql, card_id,user_id,card_id,user_id,user_id);
	}
	/**
	 * 关联查询
	 * @param page
	 * @param limit
	 * @param user_id
	 * @param card_id
	 * @param time_start
	 * @param time_end
	 * @param bank_id
	 * @return
	 */
	public ResultSet ServiceCard(String page,String limit,String user_id,String card_id,String time_start,String time_end,String bank_id) {
		String sql="SELECT\r\n" + 
				"	cdc.CreditCard_number,\r\n" + 
				"	bk.bank_name,\r\n" + 
				"	DATE_FORMAT( cdc.CreditCard_date, '%Y-%m-%d' ),\r\n" + 
				"  IF((select count(*) from Repayment where Repayment_date=cdc.CreditCard_date and CreditCard_id=cdc.CreditCard_id)>0,'已还款','未还款')\r\n" + 
				"FROM\r\n" + 
				"	`CreditCard` AS cdc\r\n" + 
				"	LEFT JOIN Bank AS bk ON bk.bank_id = cdc.Bank_id\r\n" + 
				"	LEFT JOIN Repayment AS rp ON rp.CreditCard_id = cdc.CreditCard_id \r\n" + 
				"WHERE\r\n" + 
				"	cdc.User_id ="+user_id;
		isString isString =new isString();
		
		if(!isString.IsEmpty(card_id)) {
			sql+=" AND\r\n" + 
				 " cdc.CreditCard_number='"+card_id+"'";
		}
		if(!isString.IsEmpty(time_start)) {
			sql+=" AND\r\n" + 
				 " '"+time_start+"'<=cdc.CreditCard_date";
		}
		if(!isString.IsEmpty(time_end)) {
			sql+=" AND\r\n" + 
				 " '"+time_end+"'>=cdc.CreditCard_date";
		}
		if(!isString.IsEmpty(bank_id)) {
			sql+=" AND\r\n" + 
				 " cdc.Bank_id='"+bank_id+"'";
		}
		sql+=" GROUP BY cdc.CreditCard_id\r\n" + 
			 " ORDER BY cdc.CreditCard_date" + 
			 " limit "+page+","+limit;
		ResultSet rs=conn.Items(sql);
		return rs;
	}
	/**
	 * 符合条件的总数
	 * @param user_id
	 * @param card_id
	 * @param time_start
	 * @param time_end
	 * @param bank_id
	 * @return
	 */
	public int ServiceCardCount(String user_id,String card_id,String time_start,String time_end,String bank_id) {
		String sql="SELECT count(cdc.CreditCard_number) FROM `CreditCard` as cdc\r\n" + 
				" where \r\n"+
				" cdc.User_id ="+user_id;
		isString isString =new isString();
		if(!isString.IsEmpty(card_id)) {
			sql+=" AND\r\n" + 
				 " cdc.CreditCard_number='"+card_id+"'";
		}
		if(!isString.IsEmpty(time_start)) {
			sql+=" AND\r\n" + 
				 " '"+time_start+"'<=cdc.CreditCard_date";
		}
		if(!isString.IsEmpty(time_end)) {
			sql+=" AND\r\n" + 
				 " '"+time_end+"'>=cdc.CreditCard_date";
		}
		if(!isString.IsEmpty(bank_id)) {
			sql+=" AND\r\n" + 
				 " cdc.Bank_id='"+bank_id+"'";
		}
		return Integer.parseInt(conn.OneItem(sql).toString());
	}
	/**
	 * 查询指定用户的指定卡号信息
	 * @param user_id
	 * @param id
	 * @return
	 */
	public Object[] CardMsg(String user_id,String id) {
		String sql="SELECT CreditCard_id,CreditCard_number,Bank_id,DATE_FORMAT( CreditCard_date, '%Y-%m-%d' ) FROM `CreditCard` where User_id=? and CreditCard_number=? ";
		Object[] obj=conn.RowItem(sql, user_id,id);
		return obj;
	}
	/**
	 * 修改信用卡信息
	 * @param user_id
	 * @param id
	 * @param time
	 * @param bank
	 * @return
	 */
	public int ModifyCreditCard(String user_id,String id,String time,String bank) {
		String sql="Update CreditCard set CreditCard_date=? , Bank_id=? where CreditCard_id=? and User_id=?";
		return conn.DelUpInsert(sql, time,bank,id,user_id);
	}
	/**
	 * 删除指定信用卡
	 * @param user_id
	 * @param card_id
	 * @return
	 */
	public int DelCreditCard(String user_id,String card_id) {
		String sql="delete from CreditCard where User_id=? and CreditCard_number=?";
		
		return conn.DelUpInsert(sql, user_id,card_id);
	}
}
